{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### songs.csv：歌曲元数据信息，用unicode编码\n",
    "\n",
    "song_id：歌曲id\n",
    "\n",
    "song_length: 单位为ms\n",
    "\n",
    "genre_ids: genre 类别. 可多选，用 “|“隔开\n",
    "\n",
    "artist_name：歌手\n",
    "\n",
    "composer：作曲\n",
    "\n",
    "lyricist：作词\n",
    "\n",
    "language：语言\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>song_id</th>\n",
       "      <th>song_length</th>\n",
       "      <th>genre_ids</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>composer</th>\n",
       "      <th>lyricist</th>\n",
       "      <th>language</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CXoTN1eb7AI+DntdU1vbcwGRV4SCIDxZu+YD8JP8r4E=</td>\n",
       "      <td>247640</td>\n",
       "      <td>465</td>\n",
       "      <td>張信哲 (Jeff Chang)</td>\n",
       "      <td>董貞</td>\n",
       "      <td>何啟弘</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>o0kFgae9QtnYgRkVPqLJwa05zIhRlUjfF7O1tDw0ZDU=</td>\n",
       "      <td>197328</td>\n",
       "      <td>444</td>\n",
       "      <td>BLACKPINK</td>\n",
       "      <td>TEDDY|  FUTURE BOUNCE|  Bekuh BOOM</td>\n",
       "      <td>TEDDY</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DwVvVurfpuz+XPuFvucclVQEyPqcpUkHR0ne1RQzPs0=</td>\n",
       "      <td>231781</td>\n",
       "      <td>465</td>\n",
       "      <td>SUPER JUNIOR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>dKMBWoZyScdxSkihKG+Vf47nc18N9q4m58+b4e7dSSE=</td>\n",
       "      <td>273554</td>\n",
       "      <td>465</td>\n",
       "      <td>S.H.E</td>\n",
       "      <td>湯小康</td>\n",
       "      <td>徐世珍</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>W3bqWd3T+VeHFzHAUfARgW9AvVRaF4N5Yzm4Mr6Eo/o=</td>\n",
       "      <td>140329</td>\n",
       "      <td>726</td>\n",
       "      <td>貴族精選</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>52.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        song_id  song_length genre_ids  \\\n",
       "0  CXoTN1eb7AI+DntdU1vbcwGRV4SCIDxZu+YD8JP8r4E=       247640       465   \n",
       "1  o0kFgae9QtnYgRkVPqLJwa05zIhRlUjfF7O1tDw0ZDU=       197328       444   \n",
       "2  DwVvVurfpuz+XPuFvucclVQEyPqcpUkHR0ne1RQzPs0=       231781       465   \n",
       "3  dKMBWoZyScdxSkihKG+Vf47nc18N9q4m58+b4e7dSSE=       273554       465   \n",
       "4  W3bqWd3T+VeHFzHAUfARgW9AvVRaF4N5Yzm4Mr6Eo/o=       140329       726   \n",
       "\n",
       "        artist_name                            composer     lyricist  language  \n",
       "0  張信哲 (Jeff Chang)                                  董貞          何啟弘       3.0  \n",
       "1         BLACKPINK  TEDDY|  FUTURE BOUNCE|  Bekuh BOOM        TEDDY      31.0  \n",
       "2      SUPER JUNIOR                                 NaN          NaN      31.0  \n",
       "3             S.H.E                                 湯小康          徐世珍       3.0  \n",
       "4              貴族精選                         Traditional  Traditional      52.0  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "path = 'data/'\n",
    "songs = pd.read_csv(path + 'songs.csv')\n",
    "songs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2296320 entries, 0 to 2296319\n",
      "Data columns (total 7 columns):\n",
      "song_id        object\n",
      "song_length    int64\n",
      "genre_ids      object\n",
      "artist_name    object\n",
      "composer       object\n",
      "lyricist       object\n",
      "language       float64\n",
      "dtypes: float64(1), int64(1), object(5)\n",
      "memory usage: 122.6+ MB\n"
     ]
    }
   ],
   "source": [
    "songs.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "song_id              0\n",
       "song_length          0\n",
       "genre_ids        94116\n",
       "artist_name          0\n",
       "composer       1071354\n",
       "lyricist       1945268\n",
       "language             1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "songs.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. genre_ids存在缺失值，加上题目中就提醒的存在多个类别放在一个字段中，需要处理\n",
    "### 2. composer，lyricist缺失值都将近一半，这里不建议填充，因为这会导致产生歧义"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "song_id 2296320\n",
      "genre_ids 1046\n",
      "artist_name 222363\n",
      "composer 329824\n",
      "lyricist 110926\n",
      "language 11\n"
     ]
    }
   ],
   "source": [
    "values = ['song_id','genre_ids','artist_name','composer','lyricist','language']\n",
    "for i in values:\n",
    "    print(i,len(songs[i].unique()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. 歌曲信息未重复\n",
    "### 2. 类别信息明确了又多选值，要分开处理\n",
    "### 3. 演唱者，词曲作者也要看是否有多个值----如何看？\n",
    "### 4. 语言很好处理，就11个，直接查看是否有异常值即可"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " 52.0    1336694\n",
      "-1.0      639467\n",
      " 3.0      106295\n",
      " 17.0      92518\n",
      " 24.0      41744\n",
      " 31.0      39201\n",
      " 10.0      15482\n",
      " 45.0      14435\n",
      " 59.0       8098\n",
      " 38.0       2385\n",
      "Name: language, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(songs['language'].value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>song_id</th>\n",
       "      <th>song_length</th>\n",
       "      <th>genre_ids</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>composer</th>\n",
       "      <th>lyricist</th>\n",
       "      <th>language</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>605127</th>\n",
       "      <td>nMZ7IRARPBit0ZGegNfecsx77LQSpH2ZY93vyd5xRy0=</td>\n",
       "      <td>178654</td>\n",
       "      <td>444</td>\n",
       "      <td>JONGHYUN</td>\n",
       "      <td>Korean Lyrics by Kim| Jong Hyun / Lee| Yoon Se...</td>\n",
       "      <td>31</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             song_id  song_length genre_ids  \\\n",
       "605127  nMZ7IRARPBit0ZGegNfecsx77LQSpH2ZY93vyd5xRy0=       178654       444   \n",
       "\n",
       "       artist_name                                           composer  \\\n",
       "605127    JONGHYUN  Korean Lyrics by Kim| Jong Hyun / Lee| Yoon Se...   \n",
       "\n",
       "       lyricist  language  \n",
       "605127       31       NaN  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "language_miss=songs[songs['language'].isnull()]\n",
    "language_miss"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 看这条数据，语言应该是韩语，但是总感觉是lyricist是缺失，因为韩语的代号就是31，这里直接换成31，如果删除的话，怕训练数据表联表的时候没有这条记录"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "songs['language'] = songs['language'].replace(np.NAN, '31.0')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 处理类别"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1046\n"
     ]
    }
   ],
   "source": [
    "genre=songs['genre_ids'].values\n",
    "genre_list=set(genre)\n",
    "gl_len=len(genre_list)\n",
    "print(gl_len)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 包含|的情况下有1046个类别"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2380\n",
      "192\n"
     ]
    }
   ],
   "source": [
    "gl_new=[];\n",
    "for i in genre_list:\n",
    "    j=str(i)\n",
    "    if \"|\" in j:\n",
    "        j_new=j.split(\"|\")\n",
    "        #print(j_new)\n",
    "        for k in j_new:\n",
    "            gl_new.append(k)\n",
    "    else:\n",
    "        gl_new.append(j)\n",
    "            \n",
    "            \n",
    "print(len(gl_new))\n",
    "gl_new=set(gl_new)\n",
    "print(len(gl_new))  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 逐个处理1046中的类别，单类直接加入新的list，多类拆分后加入，利用集合去重，最后留下192个类别"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "genre_colums =  [\"genre_id_\"+str(i) for i in gl_new] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>song_id</th>\n",
       "      <th>genre_id_177</th>\n",
       "      <th>genre_id_458</th>\n",
       "      <th>genre_id_2100</th>\n",
       "      <th>genre_id_1096</th>\n",
       "      <th>genre_id_2107</th>\n",
       "      <th>genre_id_488</th>\n",
       "      <th>genre_id_1138</th>\n",
       "      <th>genre_id_2157</th>\n",
       "      <th>genre_id_402</th>\n",
       "      <th>...</th>\n",
       "      <th>genre_id_451</th>\n",
       "      <th>genre_id_2219</th>\n",
       "      <th>genre_id_2206</th>\n",
       "      <th>genre_id_2150</th>\n",
       "      <th>genre_id_1026</th>\n",
       "      <th>genre_id_381</th>\n",
       "      <th>genre_id_109</th>\n",
       "      <th>genre_id_1007</th>\n",
       "      <th>genre_id_94</th>\n",
       "      <th>genre_id_1287</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CXoTN1eb7AI+DntdU1vbcwGRV4SCIDxZu+YD8JP8r4E=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>o0kFgae9QtnYgRkVPqLJwa05zIhRlUjfF7O1tDw0ZDU=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DwVvVurfpuz+XPuFvucclVQEyPqcpUkHR0ne1RQzPs0=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>dKMBWoZyScdxSkihKG+Vf47nc18N9q4m58+b4e7dSSE=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>W3bqWd3T+VeHFzHAUfARgW9AvVRaF4N5Yzm4Mr6Eo/o=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 193 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        song_id genre_id_177 genre_id_458  \\\n",
       "0  CXoTN1eb7AI+DntdU1vbcwGRV4SCIDxZu+YD8JP8r4E=            0            0   \n",
       "1  o0kFgae9QtnYgRkVPqLJwa05zIhRlUjfF7O1tDw0ZDU=            0            0   \n",
       "2  DwVvVurfpuz+XPuFvucclVQEyPqcpUkHR0ne1RQzPs0=            0            0   \n",
       "3  dKMBWoZyScdxSkihKG+Vf47nc18N9q4m58+b4e7dSSE=            0            0   \n",
       "4  W3bqWd3T+VeHFzHAUfARgW9AvVRaF4N5Yzm4Mr6Eo/o=            0            0   \n",
       "\n",
       "  genre_id_2100 genre_id_1096 genre_id_2107 genre_id_488 genre_id_1138  \\\n",
       "0             0             0             0            0             0   \n",
       "1             0             0             0            0             0   \n",
       "2             0             0             0            0             0   \n",
       "3             0             0             0            0             0   \n",
       "4             0             0             0            0             0   \n",
       "\n",
       "  genre_id_2157 genre_id_402      ...      genre_id_451 genre_id_2219  \\\n",
       "0             0            0      ...                 0             0   \n",
       "1             0            0      ...                 0             0   \n",
       "2             0            0      ...                 0             0   \n",
       "3             0            0      ...                 0             0   \n",
       "4             0            0      ...                 0             0   \n",
       "\n",
       "  genre_id_2206 genre_id_2150 genre_id_1026 genre_id_381 genre_id_109  \\\n",
       "0             0             0             0            0            0   \n",
       "1             0             0             0            0            0   \n",
       "2             0             0             0            0            0   \n",
       "3             0             0             0            0            0   \n",
       "4             0             0             0            0            0   \n",
       "\n",
       "  genre_id_1007 genre_id_94 genre_id_1287  \n",
       "0             0           0             0  \n",
       "1             0           0             0  \n",
       "2             0           0             0  \n",
       "3             0           0             0  \n",
       "4             0           0             0  \n",
       "\n",
       "[5 rows x 193 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "songlen=len(songs['song_id'])\n",
    "#print(songlen)\n",
    "zero_data=np.zeros((songlen,len(genre_colums)), dtype=int)\n",
    "song_genre=pd.DataFrame(zero_data)\n",
    "song_genre.columns = [genre_colums]\n",
    "song_genre.insert(0,'song_id',songs['song_id'])\n",
    "song_genre.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2296320 entries, 0 to 2296319\n",
      "Columns: 193 entries, (song_id,) to (genre_id_1287,)\n",
      "dtypes: int32(192), object(1)\n",
      "memory usage: 1.7+ GB\n"
     ]
    }
   ],
   "source": [
    "song_genre.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2296320"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "song_len=len(songs['song_id'].unique())\n",
    "song_len"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#此函数还有问题\n",
    "for i in genre:\n",
    "    k=str(i)\n",
    "    print(k)\n",
    "    print(k.index)\n",
    "    if \"|\" in k:\n",
    "        k_new=k.split(\"|\")\n",
    "        #print(k_new)\n",
    "        for v in k_new:\n",
    "            #print(v)\n",
    "            song_genre[j,'genre_id_'+str(v)]=1\n",
    "    else:\n",
    "        #song_genre[i]['genre_id_'+str(j)]=1\n",
    "        song_genre[j,'genre_id_'+str(i)]=1\n",
    "    if (i%1000==0):\n",
    "            print('now process is:'+str(i))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 采用区间计数，计算每个类别的出现概率\n",
    "1. 特征维度不变\n",
    "2. 将类别转变成出现的概率\n",
    "3. 如果是单类别，则是该类别出现的次数除以总的类别数，不是行数\n",
    "4. 多类别是所有单类别的概率和"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getBinCounting(name):\n",
    "    name_data=songs[name]\n",
    "    name_data_len=len(name_data)\n",
    "    name_values=songs[name].values\n",
    "    name_list=[]\n",
    "    for i in name_values:\n",
    "        j=str(i)\n",
    "        if \"|\" in j:\n",
    "            j_new=j.split(\"|\")\n",
    "            #print(j_new)\n",
    "            for k in j_new:\n",
    "                name_list.append(k)\n",
    "        else:\n",
    "            name_list.append(j)\n",
    "    name_len=len(name_list)\n",
    "    name_value_dict=pd.value_counts(name_list).to_dict()\n",
    "    if(name=='genre_ids'):\n",
    "        name_len=name_len-name_value_dict['nan']\n",
    "    print(name_len)\n",
    "    for key,value in name_value_dict.items():\n",
    "        #print (\"key:[%s],value:[%s]\"%(key,value))\n",
    "        name_value_dict[key]=name_value_dict[key]/name_len\n",
    "        name_data[name_data==key]=name_value_dict[key]\n",
    "    print('start change |')\n",
    "    for i in range(name_data_len):\n",
    "        j=str(name_data[i])\n",
    "        #print(j)\n",
    "        bc_value=0\n",
    "        if \"|\" in j:\n",
    "            j_new=j.split(\"|\")\n",
    "            #print(j_new)\n",
    "            for k in j_new:\n",
    "                bc_value=bc_value+name_value_dict[k]\n",
    "            name_data[i]=bc_value\n",
    "        if i%10000==0:\n",
    "            print(i)\n",
    "    return name_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2420580\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\15067\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:23: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "start change |\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\15067\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:34: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    }
   ],
   "source": [
    "genre_bc=getBinCounting('genre_ids')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>genre_BinCounting</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.243421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.00665006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.243421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.243421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.0151889</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  genre_BinCounting\n",
       "0          0.243421\n",
       "1        0.00665006\n",
       "2          0.243421\n",
       "3          0.243421\n",
       "4         0.0151889"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "genre_end=pd.DataFrame(genre_bc)\n",
    "genre_end.columns=['genre_BinCounting']\n",
    "genre_end.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>song_id</th>\n",
       "      <th>genre_ids</th>\n",
       "      <th>genre_BinCounting</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CXoTN1eb7AI+DntdU1vbcwGRV4SCIDxZu+YD8JP8r4E=</td>\n",
       "      <td>465</td>\n",
       "      <td>0.243421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>o0kFgae9QtnYgRkVPqLJwa05zIhRlUjfF7O1tDw0ZDU=</td>\n",
       "      <td>444</td>\n",
       "      <td>0.00665006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DwVvVurfpuz+XPuFvucclVQEyPqcpUkHR0ne1RQzPs0=</td>\n",
       "      <td>465</td>\n",
       "      <td>0.243421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>dKMBWoZyScdxSkihKG+Vf47nc18N9q4m58+b4e7dSSE=</td>\n",
       "      <td>465</td>\n",
       "      <td>0.243421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>W3bqWd3T+VeHFzHAUfARgW9AvVRaF4N5Yzm4Mr6Eo/o=</td>\n",
       "      <td>726</td>\n",
       "      <td>0.0151889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>kKJ2JNU5h8rphyW21ovC+RZU+yEHPM+3w85J37p7vEQ=</td>\n",
       "      <td>864|857|850|843</td>\n",
       "      <td>0.0135839</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>N9vbanw7BSMoUgdfJlgX1aZPE1XZg8OS1wf88AQEcMc=</td>\n",
       "      <td>458</td>\n",
       "      <td>0.00737716</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>GsCpr618xfveHYJdo+E5SybrpR906tsjLMeKyrCNw8s=</td>\n",
       "      <td>465</td>\n",
       "      <td>0.243421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>oTi7oINPX+rxoGp+3O6llSltQTl80jDqHoULfRoLcG4=</td>\n",
       "      <td>465</td>\n",
       "      <td>0.243421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>btcG03OHY3GNKWccPP0auvtSbhxog/kllIIOx5grE/k=</td>\n",
       "      <td>352|1995</td>\n",
       "      <td>0.00441217</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        song_id        genre_ids  \\\n",
       "0  CXoTN1eb7AI+DntdU1vbcwGRV4SCIDxZu+YD8JP8r4E=              465   \n",
       "1  o0kFgae9QtnYgRkVPqLJwa05zIhRlUjfF7O1tDw0ZDU=              444   \n",
       "2  DwVvVurfpuz+XPuFvucclVQEyPqcpUkHR0ne1RQzPs0=              465   \n",
       "3  dKMBWoZyScdxSkihKG+Vf47nc18N9q4m58+b4e7dSSE=              465   \n",
       "4  W3bqWd3T+VeHFzHAUfARgW9AvVRaF4N5Yzm4Mr6Eo/o=              726   \n",
       "5  kKJ2JNU5h8rphyW21ovC+RZU+yEHPM+3w85J37p7vEQ=  864|857|850|843   \n",
       "6  N9vbanw7BSMoUgdfJlgX1aZPE1XZg8OS1wf88AQEcMc=              458   \n",
       "7  GsCpr618xfveHYJdo+E5SybrpR906tsjLMeKyrCNw8s=              465   \n",
       "8  oTi7oINPX+rxoGp+3O6llSltQTl80jDqHoULfRoLcG4=              465   \n",
       "9  btcG03OHY3GNKWccPP0auvtSbhxog/kllIIOx5grE/k=         352|1995   \n",
       "\n",
       "  genre_BinCounting  \n",
       "0          0.243421  \n",
       "1        0.00665006  \n",
       "2          0.243421  \n",
       "3          0.243421  \n",
       "4         0.0151889  \n",
       "5         0.0135839  \n",
       "6        0.00737716  \n",
       "7          0.243421  \n",
       "8          0.243421  \n",
       "9        0.00441217  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "song_genres = pd.concat([songs['song_id'],songs['genre_ids'],genre_end],axis=1)\n",
    "song_genres.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 缺失值之前在计算概率的时候在总数里面有去掉，但是缺失值本身的概率也算进去了，这里要去掉"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "sg_data=song_genres\n",
    "#前面没处理好，这里偷懒，直接算出缺失值的概率，然后比对置为0\n",
    "nan_per=94116/2420580\n",
    "sg_data[sg_data['genre_BinCounting']==nan_per]=0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "song_id                  0\n",
       "genre_ids            94116\n",
       "genre_BinCounting    94116\n",
       "dtype: int64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sg_data.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2296320 entries, 0 to 2296319\n",
      "Data columns (total 3 columns):\n",
      "song_id              object\n",
      "genre_ids            object\n",
      "genre_BinCounting    object\n",
      "dtypes: object(3)\n",
      "memory usage: 52.6+ MB\n"
     ]
    }
   ],
   "source": [
    "sg_data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 处理后刚好和之前缺失值的数量一致，说明没有问题"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "sg_data.to_csv(path +\"FE_song_genres_BinCounting.csv\",index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#  <font color='#ff0000'> 一个比较大的问题是，这样处理到底有没有用，对于这种区间计数的方法，怎么解释会易于理解？</font>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 方法2：将多类别取第一个，其余不变\n",
    "1. 特征维度不变\n",
    "2. 单类别保持不变\n",
    "4. 多类别取首个"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def queryDistribution(name):\n",
    "    name_valus=songs[name].values\n",
    "    len_2=0\n",
    "    len_3=0\n",
    "    len_more3=0\n",
    "    for i in name_valus:\n",
    "        k=str(i)\n",
    "        #print(k)\n",
    "        #print(k.index)\n",
    "        if \"|\" in k:\n",
    "            k_new=k.split(\"|\")\n",
    "            k_len=len(k_new)\n",
    "            if(k_len==2):\n",
    "                len_2=len_2+1\n",
    "            if(k_len==3):\n",
    "                len_3=len_3+1\n",
    "            else:\n",
    "                len_more3=len_more3+1\n",
    "    print('len_2:'+str(len_2))\n",
    "    print('len_3:'+str(len_3))\n",
    "    print('len_more3:'+str(len_more3))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "len_2:138833\n",
      "len_3:24064\n",
      "len_more3:148812\n"
     ]
    }
   ],
   "source": [
    "queryDistribution('genre_ids')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 通过查看信息，一共230w首歌，其中30w有2个种类，17w有3个种类，3个种类以上有近15w"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "E:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "genre_ids_onevalue=getOneValue('genre_ids')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>genre_ids</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>726</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  genre_ids\n",
       "0       465\n",
       "1       444\n",
       "2       465\n",
       "3       465\n",
       "4       726"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "genre_info=pd.DataFrame(genre_ids_onevalue)\n",
    "genre_info.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 处理演唱者，词曲作家"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "artist_name sum is:20942\n",
      "composer sum is:175313\n",
      "lyricist sum is:50081\n"
     ]
    }
   ],
   "source": [
    "values = ['artist_name','composer','lyricist']\n",
    "for i in values:\n",
    "    values=songs[i].values\n",
    "    datalist=set(values)\n",
    "    datasum=0\n",
    "    for j in datalist:\n",
    "        j=str(j)\n",
    "        if \"|\" in j:\n",
    "            datasum=datasum+1\n",
    "    print(i,'sum is:'+str(datasum))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 演唱者"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "len_2:44903\n",
      "len_3:29895\n",
      "len_more3:58023\n"
     ]
    }
   ],
   "source": [
    "queryDistribution('artist_name')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 一共230w数据，2类13.5w,3类8.5w,超过3类的5.8w"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 方案1： 使用区间计数计算概率"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2500972\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\15067\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:23: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    }
   ],
   "source": [
    "artist_bc=getBinCounting('artist_name')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 因为数据量太大，一直没跑完"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 方案2：舍弃部分数据，所有保留第一类\n",
    "因为演唱者里面，超过2类的只有13.5w数据，占比不到6%，先舍弃，后面有时间，添加第二类别"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getOneValue(name):\n",
    "    name_data=songs[name]\n",
    "    name_data_len=len(name_data)\n",
    "    for i in range(name_data_len):\n",
    "        k=str(name_data[i])\n",
    "        if \"|\" in k:\n",
    "            j=k.split(\"|\")\n",
    "            name_data[i]=j[0]\n",
    "    return name_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "E:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  import sys\n"
     ]
    }
   ],
   "source": [
    "artist_name_onevalue=getOneValue('artist_name')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def isHaveSign(df):\n",
    "    len_2=0\n",
    "    len_3=0\n",
    "    len_more3=0\n",
    "    for i in df:\n",
    "        k=str(i)\n",
    "        #print(k)\n",
    "        #print(k.index)\n",
    "        if \"|\" in k:\n",
    "            k_new=k.split(\"|\")\n",
    "            k_len=len(k_new)\n",
    "            if(k_len==2):\n",
    "                len_2=len_2+1\n",
    "            if(k_len==3):\n",
    "                len_3=len_3+1\n",
    "            else:\n",
    "                len_more3=len_more3+1\n",
    "    print('len_2:'+str(len_2))\n",
    "    print('len_3:'+str(len_3))\n",
    "    print('len_more3:'+str(len_more3))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "len_2:0\n",
      "len_3:0\n",
      "len_more3:0\n"
     ]
    }
   ],
   "source": [
    "isHaveSign(artist_name_onevalue)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>artist_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>張信哲 (Jeff Chang)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BLACKPINK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SUPER JUNIOR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>S.H.E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>貴族精選</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        artist_name\n",
       "0  張信哲 (Jeff Chang)\n",
       "1         BLACKPINK\n",
       "2      SUPER JUNIOR\n",
       "3             S.H.E\n",
       "4              貴族精選"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "artist_name_value=pd.DataFrame(artist_name_onevalue)\n",
    "artist_name_value.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2296320 entries, 0 to 2296319\n",
      "Data columns (total 1 columns):\n",
      "artist_name    object\n",
      "dtypes: object(1)\n",
      "memory usage: 17.5+ MB\n"
     ]
    }
   ],
   "source": [
    "artist_name_value.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "artist_info = pd.concat([songs['song_id'],artist_name_value],axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 词作者"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "E:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "composer_onevalue=getOneValue('composer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>composer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>董貞</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TEDDY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>湯小康</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Traditional</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      composer\n",
       "0           董貞\n",
       "1        TEDDY\n",
       "2          NaN\n",
       "3          湯小康\n",
       "4  Traditional"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "composer_info=pd.DataFrame(composer_onevalue)\n",
    "composer_info.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "len_2:0\n",
      "len_3:0\n",
      "len_more3:0\n"
     ]
    }
   ],
   "source": [
    "isHaveSign(composer_onevalue)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 作曲者"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "lyricist_onevalue=getOneValue('lyricist')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>lyricist</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>何啟弘</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TEDDY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>徐世珍</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Traditional</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      lyricist\n",
       "0          何啟弘\n",
       "1        TEDDY\n",
       "2          NaN\n",
       "3          徐世珍\n",
       "4  Traditional"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lyricist_info=pd.DataFrame(lyricist_onevalue)\n",
    "lyricist_info.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "lyricist_info = pd.concat([songs['song_id'],lyricist_info],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "lyricist_info.to_csv(path +\"FE_lyricist_info_toLightGBM.csv\",index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "一共230w数据，lyricist缺失190w条"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "song_info= pd.concat([songs['song_id'],songs['song_length'],genre_info,artist_name_value,composer_info,songs['language']],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>song_id</th>\n",
       "      <th>song_length</th>\n",
       "      <th>genre_ids</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>composer</th>\n",
       "      <th>language</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CXoTN1eb7AI+DntdU1vbcwGRV4SCIDxZu+YD8JP8r4E=</td>\n",
       "      <td>247640</td>\n",
       "      <td>465</td>\n",
       "      <td>張信哲 (Jeff Chang)</td>\n",
       "      <td>董貞</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>o0kFgae9QtnYgRkVPqLJwa05zIhRlUjfF7O1tDw0ZDU=</td>\n",
       "      <td>197328</td>\n",
       "      <td>444</td>\n",
       "      <td>BLACKPINK</td>\n",
       "      <td>TEDDY</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DwVvVurfpuz+XPuFvucclVQEyPqcpUkHR0ne1RQzPs0=</td>\n",
       "      <td>231781</td>\n",
       "      <td>465</td>\n",
       "      <td>SUPER JUNIOR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>dKMBWoZyScdxSkihKG+Vf47nc18N9q4m58+b4e7dSSE=</td>\n",
       "      <td>273554</td>\n",
       "      <td>465</td>\n",
       "      <td>S.H.E</td>\n",
       "      <td>湯小康</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>W3bqWd3T+VeHFzHAUfARgW9AvVRaF4N5Yzm4Mr6Eo/o=</td>\n",
       "      <td>140329</td>\n",
       "      <td>726</td>\n",
       "      <td>貴族精選</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>kKJ2JNU5h8rphyW21ovC+RZU+yEHPM+3w85J37p7vEQ=</td>\n",
       "      <td>235520</td>\n",
       "      <td>864</td>\n",
       "      <td>貴族精選</td>\n",
       "      <td>Joe Hisaishi</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>N9vbanw7BSMoUgdfJlgX1aZPE1XZg8OS1wf88AQEcMc=</td>\n",
       "      <td>226220</td>\n",
       "      <td>458</td>\n",
       "      <td>伍佰 &amp; China Blue</td>\n",
       "      <td>Jonathan Lee</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>GsCpr618xfveHYJdo+E5SybrpR906tsjLMeKyrCNw8s=</td>\n",
       "      <td>276793</td>\n",
       "      <td>465</td>\n",
       "      <td>光良 (Michael Wong)</td>\n",
       "      <td>光良</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>oTi7oINPX+rxoGp+3O6llSltQTl80jDqHoULfRoLcG4=</td>\n",
       "      <td>228623</td>\n",
       "      <td>465</td>\n",
       "      <td>林俊傑 (JJ Lin)</td>\n",
       "      <td>JJ Lin</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>btcG03OHY3GNKWccPP0auvtSbhxog/kllIIOx5grE/k=</td>\n",
       "      <td>232629</td>\n",
       "      <td>352</td>\n",
       "      <td>Kodaline</td>\n",
       "      <td>Stephen Garrigan</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        song_id  song_length genre_ids  \\\n",
       "0  CXoTN1eb7AI+DntdU1vbcwGRV4SCIDxZu+YD8JP8r4E=       247640       465   \n",
       "1  o0kFgae9QtnYgRkVPqLJwa05zIhRlUjfF7O1tDw0ZDU=       197328       444   \n",
       "2  DwVvVurfpuz+XPuFvucclVQEyPqcpUkHR0ne1RQzPs0=       231781       465   \n",
       "3  dKMBWoZyScdxSkihKG+Vf47nc18N9q4m58+b4e7dSSE=       273554       465   \n",
       "4  W3bqWd3T+VeHFzHAUfARgW9AvVRaF4N5Yzm4Mr6Eo/o=       140329       726   \n",
       "5  kKJ2JNU5h8rphyW21ovC+RZU+yEHPM+3w85J37p7vEQ=       235520       864   \n",
       "6  N9vbanw7BSMoUgdfJlgX1aZPE1XZg8OS1wf88AQEcMc=       226220       458   \n",
       "7  GsCpr618xfveHYJdo+E5SybrpR906tsjLMeKyrCNw8s=       276793       465   \n",
       "8  oTi7oINPX+rxoGp+3O6llSltQTl80jDqHoULfRoLcG4=       228623       465   \n",
       "9  btcG03OHY3GNKWccPP0auvtSbhxog/kllIIOx5grE/k=       232629       352   \n",
       "\n",
       "         artist_name          composer language  \n",
       "0   張信哲 (Jeff Chang)                董貞        3  \n",
       "1          BLACKPINK             TEDDY       31  \n",
       "2       SUPER JUNIOR               NaN       31  \n",
       "3              S.H.E               湯小康        3  \n",
       "4               貴族精選       Traditional       52  \n",
       "5               貴族精選      Joe Hisaishi       17  \n",
       "6    伍佰 & China Blue      Jonathan Lee        3  \n",
       "7  光良 (Michael Wong)                光良        3  \n",
       "8       林俊傑 (JJ Lin)            JJ Lin        3  \n",
       "9           Kodaline  Stephen Garrigan       52  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "song_info.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2296320 entries, 0 to 2296319\n",
      "Data columns (total 6 columns):\n",
      "song_id        object\n",
      "song_length    int64\n",
      "genre_ids      object\n",
      "artist_name    object\n",
      "composer       object\n",
      "language       object\n",
      "dtypes: int64(1), object(5)\n",
      "memory usage: 105.1+ MB\n"
     ]
    }
   ],
   "source": [
    "song_info.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "song_info.to_csv(path +\"FE_song_OneValue.csv\",encoding='utf-8',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 这里一开始没有处理lyricist这个字段，所以一开始保存的数据里没有这个，后续单独处理保存了lyricist的处理数据"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
